-- @owner: @zhanghuan96
-- @date: 2024/11/12
-- @testpoint: 解析的对象名和参数2的值不匹配，部分合理报错
@conn PrimaryDbAmode;
--前置条件
drop extension if exists gms_utility;
create extension gms_utility;
--step1:对象是表，使用函数;expect:参数2为0,2,7成功，其余报错
drop table if exists t_resolve0018;
create table t_resolve0018(a int);

DECLARE
    name VARCHAR2(100) := 'public.t_resolve0018';
    context NUMBER;
    schema VARCHAR2(100);
    part1 VARCHAR2(100);
    part2 VARCHAR2(100);
    dblink VARCHAR2(100);
    part1_type NUMBER;
    object_number NUMBER;
BEGIN
    FOR context IN 0..10 LOOP
        BEGIN
            gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
             RAISE INFO 'INFO:context = %, schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %',
               context, schema, part1, part2, dblink, part1_type, object_number;
        EXCEPTION
            WHEN OTHERS THEN
                RAISE INFO 'INFO:Error occurred for context %: %', context, SQLERRM;
        END;
    END LOOP;
END;
/
--step2:对象是视图;expect:参数2为0,2,7成功，其余报错
drop view if exists v_resolve0018;
create view v_resolve0018 as select * from t_resolve0018;

DECLARE
    name VARCHAR2(100) := 'public.v_resolve0018';
    context NUMBER;
    schema VARCHAR2(100);
    part1 VARCHAR2(100);
    part2 VARCHAR2(100);
    dblink VARCHAR2(100);
    part1_type NUMBER;
    object_number NUMBER;
BEGIN
    FOR context IN 0..10 LOOP
        BEGIN
            gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
            RAISE INFO 'INFO:context = %, schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %',
               context, schema, part1, part2, dblink, part1_type, object_number;
        EXCEPTION
            WHEN OTHERS THEN
                RAISE INFO 'INFO:Error occurred for context %: %', context, SQLERRM;
        END;
    END LOOP;
End;
/
--step3:对象是索引;expect:参数2为9成功，其余报错
drop index if exists idx_tb_dlsy_idx;
create index idx_tb_dlsy_idx on public.t_resolve0018(a);

DECLARE
    name VARCHAR2(100) := 'public.idx_tb_dlsy_idx';
    context NUMBER;
    schema VARCHAR2(100);
    part1 VARCHAR2(100);
    part2 VARCHAR2(100);
    dblink VARCHAR2(100);
    part1_type NUMBER;
    object_number NUMBER;
BEGIN
    FOR context IN 0..10 LOOP
        BEGIN
            gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
            RAISE INFO 'INFO:context = %, schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %',
               context, schema, part1, part2, dblink, part1_type, object_number;
        EXCEPTION
            WHEN OTHERS THEN
                RAISE INFO 'INFO:Error occurred for context %: %', context, SQLERRM;
        END;
    END LOOP;
End;
/
--step4:对象是序列;expect:参数2为2,7成功，其余报错
drop sequence if exists t_seq;
CREATE SEQUENCE IF NOT EXISTS public.t_seq
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    START WITH 1
    NOCYCLE;
SELECT public.t_seq.NEXTVAL;

DECLARE
    name VARCHAR2(100) := 'public.t_seq';
    context NUMBER;
    schema VARCHAR2(100);
    part1 VARCHAR2(100);
    part2 VARCHAR2(100);
    dblink VARCHAR2(100);
    part1_type NUMBER;
    object_number NUMBER;
BEGIN
    FOR context IN 0..10 LOOP
        BEGIN
            gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
            RAISE INFO 'INFO:context = %, schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %',
               context, schema, part1, part2, dblink, part1_type, object_number;
        EXCEPTION
            WHEN OTHERS THEN
                RAISE INFO 'INFO:Error occurred for context %: %', context, SQLERRM;
        END;
    END LOOP;
End;
/
--step5:对象是自定义函数;expect:参数2为1成功，其余报错
drop function if exists add_numbers1;
CREATE OR REPLACE FUNCTION public.add_numbers1 (
    p_num1 IN INT,
    p_num2 IN INT
) RETURN NUMBER IS
    v_result INT;
BEGIN
    v_result := p_num1 + p_num2;
    RETURN v_result;
END;
/

DECLARE
    name VARCHAR2(100) := 'public.add_numbers1';
    context NUMBER;
    schema VARCHAR2(100);
    part1 VARCHAR2(100);
    part2 VARCHAR2(100);
    dblink VARCHAR2(100);
    part1_type NUMBER;
    object_number NUMBER;
BEGIN
    FOR context IN 0..10 LOOP
        BEGIN
            gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
            RAISE INFO 'INFO:context = %, schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %',
               context, schema, part1, part2, dblink, part1_type, object_number;
        EXCEPTION
            WHEN OTHERS THEN
                RAISE INFO 'INFO:Error occurred for context %: %', context, SQLERRM;
        END;
    END LOOP;
End;
/
--step6:对象是存储过程;expect:参数2为1成功，其余报错
drop procedure if exists insert_val1;
CREATE OR REPLACE PROCEDURE public.insert_val1(name VARCHAR2(100)) IS
	id INT := 0;
BEGIN
	SELECT public.t_seq.NEXTVAL INTO id;
	INSERT INTO public.t_resolve VALUES (id, name);
END;
/

DECLARE
    name VARCHAR2(100) := 'public.insert_val1';
    context NUMBER;
    schema VARCHAR2(100);
    part1 VARCHAR2(100);
    part2 VARCHAR2(100);
    dblink VARCHAR2(100);
    part1_type NUMBER;
    object_number NUMBER;
BEGIN
    FOR context IN 0..10 LOOP
        BEGIN
            gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
            RAISE INFO 'INFO:context = %, schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %',
               context, schema, part1, part2, dblink, part1_type, object_number;
        EXCEPTION
            WHEN OTHERS THEN
                RAISE INFO 'INFO:Error occurred for context %: %', context, SQLERRM;
        END;
    END LOOP;
End;
/
--step7:对象是自定义类型;expect:参数2为7成功，其余报错
drop type if exists type_resolve0016_01;
CREATE TYPE type_resolve0016_01 AS (
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE
);

DECLARE
    name VARCHAR2(100) := 'public.type_resolve0016_01';
    context NUMBER;
    schema VARCHAR2(100);
    part1 VARCHAR2(100);
    part2 VARCHAR2(100);
    dblink VARCHAR2(100);
    part1_type NUMBER;
    object_number NUMBER;
BEGIN
    FOR context IN 0..10 LOOP
        BEGIN
            gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
            RAISE INFO 'INFO:context = %, schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %',
               context, schema, part1, part2, dblink, part1_type, object_number;
        EXCEPTION
            WHEN OTHERS THEN
                RAISE INFO 'INFO:Error occurred for context %: %', context, SQLERRM;
        END;
    END LOOP;
End;
/
--step8:对象是表的同义词;expect:参数2为0,2,7成功，其余报错
drop synonym if exists emp_syn;
create synonym public.emp_syn for public.t_resolve0018;

DECLARE
    name VARCHAR2(100) := 'EMP_SYN';
    context NUMBER;
    schema VARCHAR2(100);
    part1 VARCHAR2(100);
    part2 VARCHAR2(100);
    dblink VARCHAR2(100);
    part1_type NUMBER;
    object_number NUMBER;
BEGIN
    FOR context IN 0..10 LOOP
        BEGIN
            gms_utility.NAME_RESOLVE(name, context, schema, part1, part2, dblink, part1_type, object_number);
            RAISE INFO 'INFO:context = %, schema = %, part1 = %, part2 = %, dblink = %, part1_type = %, object_number = %',
               context, schema, part1, part2, dblink, part1_type, object_number;
        EXCEPTION
            WHEN OTHERS THEN
                RAISE INFO 'INFO:Error occurred for context %: %', context, SQLERRM;
        END;
    END LOOP;
End;
/
--step9:清理环境;expect:成功
drop table if exists t_resolve0018;
drop view if exists v_resolve0018;
drop index if exists idx_tb_dlsy_idx;
drop sequence if exists t_seq;
drop function if exists add_numbers1;
drop procedure if exists insert_val1;
drop synonym if exists emp_syn;
drop extension if exists gms_utility;